package com.c2soft.nepalcmx.dao;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.c2soft.nepalcmx.command.AccountDetailCommand;
import com.c2soft.nepalcmx.command.ConfirmTradeCommand;
import com.c2soft.nepalcmx.command.NetPositionCommand;





public class AccountDetailDAO {
	
	@Autowired
	private DataSource dataSource;
	
	private JdbcTemplate jdbcTemplate;
	
	public boolean saveAccountDetailService(AccountDetailCommand account) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		String findUser="select USER_ID from brokers where broker_id='"+account.getBrokerId()+"' and USER_ID!='' ";
		List<AccountDetailCommand> accounts=jdbcTemplate.query(findUser,new BeanPropertyRowMapper(AccountDetailCommand.class));
		Iterator itr=accounts.iterator();
		boolean a=false;
		while(itr.hasNext()){
			AccountDetailCommand acc=(AccountDetailCommand)itr.next();
			
			if(acc.getUserId().equals(account.getUserId())){
				a=true;
				break;
				
			}
		}
		
		if(a==true){	
			/*String sql = "INSERT INTO accountdetails (BROKER_ID,USER_ID, BNAME,ACCNO,PANCARD,OPENBAL,BROKAGE,CASHL,MARGINL,CREDITAMT,DEBITAMT,BILLNO,CHEQUENO,BADDRESS) VALUES (?,?,?,?,?,?, ?, ?,?,?,?,?,?,?)";
				
				
				jdbcTemplate.update(sql,
						new Object[] { account.getBrokerId(),account.getUserId(),account.getBname(),account.getAccno(), account.getPancard(),
						account.getOpenbal(), account.getBrokage(), account.getCashl(),
						account.getMarginl(),account.getCreditamt(), account.getDebitamt(), account.getBillno(),account.getChequeno(),account.getBaddress()});
			*/
				String sql1 = "UPDATE brokers set BNAME=?,ACCNO=?,PANCARD=?,OPENBAL=?,BROKAGE=?,CASHL=?,MARGINL=?,"
						+ "CREDITAMT=?,DEBITAMT=?,IFSC=?,"
						+ "BILLNO=?,CHEQUENO=?,BADDRESS=? where USER_ID=? and BROKER_ID=?";
						
					
					
					jdbcTemplate.update(sql1,
							new Object[] {account.getBname(),account.getAccno(), account.getPancard(),
							account.getOpenbal(), account.getBrokage(), account.getCashl(),
							account.getMarginl(),account.getCreditamt(), account.getDebitamt(), account.getIfsc(),account.getBillno(),account.getChequeno(),
							account.getBaddress(),account.getUserId(), account.getBrokerId()});
				
			String sql2="INSERT INTO account(BROKER_ID,USER_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,IFSC,remark) values(?,?,?,?,?,?,?,'Account Open.')";
				jdbcTemplate.update(sql2,
						new Object[] { account.getBrokerId(),account.getUserId(),account.getAccno(),account.getCashl(),account.getMarginl(),account.getMarginl(),account.getIfsc()});
			
				
			
		}
		return a;
		
	}

	/*********************************************************/
	public boolean saveBrokerAccountDetailService(AccountDetailCommand account) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		String findUser="select broker_ID from brokers where user_id='' and broker_id!='' ";
		List<AccountDetailCommand> accounts=jdbcTemplate.query(findUser,new BeanPropertyRowMapper(AccountDetailCommand.class));
		Iterator itr=accounts.iterator();
		boolean a=false;
		while(itr.hasNext()){
			AccountDetailCommand acc=(AccountDetailCommand)itr.next();
			
			
			 if(acc.getBrokerId().equals(account.getBrokerId())){
				a=true;
				break;
				
			}
		}
		
		if(a==true){	
		
		/*String sql = "INSERT INTO accountdetails (BROKER_ID, BNAME,ACCNO,PANCARD,OPENBAL,BROKAGE,CASHL,MARGINL,CREDITAMT,DEBITAMT,BILLNO,CHEQUENO,BADDRESS) VALUES (?,?,?,?,?, ?, ?,?,?,?,?,?,?)";
		
		
		jdbcTemplate.update(sql,
				new Object[] { account.getBrokerId(),account.getBname(),account.getAccno(), account.getPancard(),
				account.getOpenbal(), account.getBrokage(), account.getCashl(),
				account.getMarginl(),account.getCreditamt(), account.getDebitamt(), account.getBillno(),account.getChequeno(),account.getBaddress()});
		*/
		String sql1 = "UPDATE brokers set BNAME=?,ACCNO=?,PANCARD=?,OPENBAL=?,BROKAGE=?,CASHL=?,MARGINL=?,"
						+ "CREDITAMT=?,DEBITAMT=?,IFSC=?,"
						+ "BILLNO=?,CHEQUENO=?,BADDRESS=? where BROKER_ID=? and user_id=''";
						
					
					
					jdbcTemplate.update(sql1,
							new Object[] {account.getBname(),account.getAccno(), account.getPancard(),
							account.getOpenbal(), account.getBrokage(), account.getCashl(),
							account.getMarginl(),account.getCreditamt(), account.getDebitamt(), account.getIfsc(),account.getBillno(),account.getChequeno(),
							account.getBaddress(), account.getBrokerId()});
				
		
		
		String sql2="INSERT INTO account(BROKER_ID,IFSC,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,remark) values(?,?,?,?,?,?,'Account Open')";
		jdbcTemplate.update(sql2,
				new Object[] { account.getBrokerId(),account.getIfsc(),account.getAccno(),account.getCashl(),account.getMarginl(),account.getMarginl()});
		}
		return a;
		
	}
	/**********************************************/
	
	public void saveAccountDetailServiceX(AccountDetailCommand account) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		
				String sql1 = "UPDATE brokers set BNAME=?,ACCNO=?,PANCARD=?,OPENBAL=?,BROKAGE=?,CASHL=?,MARGINL=?,"
						+ "CREDITAMT=?,DEBITAMT=?,IFSC=?,"
						+ "BILLNO=?,CHEQUENO=?,BADDRESS=? where USER_ID='' and BROKER_ID=''";
						
					
					
			jdbcTemplate.update(sql1,
							new Object[] {account.getBname(),account.getAccno(), account.getPancard(),
							account.getOpenbal(), account.getBrokage(), account.getCashl(),
							account.getMarginl(),account.getCreditamt(), account.getDebitamt(),account.getIfsc() ,account.getBillno(),account.getChequeno(),
							account.getBaddress()});
				
			String sql2="INSERT INTO account(EXCHANGE_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,IFSC,remark) values(?,?,?,?,?,?,'Account Open')";
				jdbcTemplate.update(sql2,
						new Object[] { "exchange",account.getAccno(),account.getCashl(),account.getMarginl(),account.getMarginl(),account.getIfsc()});
		
		
	}
	public void updateAccountDetailService(AccountDetailCommand account) {
		String sql = "UPDATE brokers SET BNAME='"+account.getBname()+"',ACCNO='"+account.getAccno()+"',"
				+ "BROKAGE='"+account.getBrokage()+"',CASHL='"+account.getCashl()+"',MARGINL='"+account.getMarginl()+"',"
						+ "CREDITAMT='"+account.getCreditamt()+"',DEBITAMT='"+ account.getDebitamt()+"',IFSC='"+account.getIfsc()
								+ "',BILLNO='"+account.getBillno()+"',CHEQUENO='"+account.getChequeno()+"',BADDRESS='"+account.getBaddress()+
								"' WHERE USER_ID='"+ account.getUserId()+"' and BROKER_ID='"+account.getBrokerId()+"'";
		
		jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql);
		String sql1="INSERT INTO account(BROKER_ID,USER_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,IFSC,remark) values(?,?,?,?,?,?,?,'Account Updated')";
		jdbcTemplate.update(sql1,
				new Object[] { account.getBrokerId(),account.getUserId(),account.getAccno(),account.getCashl(),account.getMarginl(),account.getMarginl(),account.getIfsc()});	
		
	}
	
	/*public List<AccountDetailCommand> findAllAccountDetails(String brokerId) {
		String sql = "select * from accountdetails where broker_id='"+brokerId+"' and user_id is not null";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new BeanPropertyRowMapper(AccountDetailCommand.class));
		return accounts;

	}*/
	
	
	
	public List<AccountDetailCommand> findAccountDetails(String userId) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		int maxId = jdbcTemplate.queryForInt("select max(id) from account where user_Id=? ",userId);
		System.out.println("A");
		
		String sql = "select cashl,marginl,creditamt,debitamt,BALANCED_MARGIN,UTILISED_MARGIN from account where user_Id=? and id="+maxId;
		
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{userId},new CashFundViewMapper());

		
		return accounts;

	}

	private final class CashFundViewMapper implements RowMapper<AccountDetailCommand> {

	    public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	AccountDetailCommand acc = new AccountDetailCommand();
	    	acc.setCashl(rs.getString(1));
	    	acc.setMarginl(rs.getString(2));
	    	acc.setCreditamt(rs.getString(3));
	    	acc.setDebitamt(rs.getString(4));
	    	acc.setMargin_b(rs.getString(5));
	    	acc.setMargin_u(rs.getString(6));
	        return acc;
	     
	}
	}
	public List<AccountDetailCommand> findAccountDetails1(String userId,String brokerId) {
		String sql = "select * from brokers where user_Id=? and broker_id=?";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{userId,brokerId},new BeanPropertyRowMapper(AccountDetailCommand.class));
		return accounts;

	}

	public List<AccountDetailCommand> findUserAccountDetails(String userId) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		int maxId = jdbcTemplate.queryForInt("select max(id) from account where user_Id=? ",userId);
		String sql = "select CASHL,MARGINL,CREDITAMT,DEBITAMT,BALANCED_MARGIN,UTILISED_MARGIN,ACCNO,USER_ID from account where user_Id=? and id="+maxId+" ";
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{userId},new AccountMapper());
		return accounts;

	}
	
	private final class AccountMapper implements RowMapper<AccountDetailCommand> {

	    public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	AccountDetailCommand acc = new AccountDetailCommand();
	    	acc.setCashl(rs.getString(1));
	    	acc.setMarginl(rs.getString(2));
	    	acc.setCreditamt(rs.getString(3));
	    	acc.setDebitamt(rs.getString(4));
	    	acc.setMargin_b(rs.getString(5));
	    	acc.setAccno(rs.getString(7));
	    	acc.setMargin_u(rs.getString(6));
	    	acc.setUserId(rs.getString(8));
	    	
	        return acc;
	     
	}
	}

	    
	public List<AccountDetailCommand> findAllAccountDetails(String userId,ConfirmTradeCommand confirmTrade) throws ParseException {
		String fromDate=confirmTrade.getFromDate();
		String toDate=confirmTrade.getToDate();
		String symbol=confirmTrade.getSymbol();;
		String qty=confirmTrade.getQty();
		
		
		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		Date d1=(Date) formatter.parse(fromDate);		
		Date d2=(Date) formatter.parse(toDate);
		
		String sql = "select * from account where user_Id=? and DATE(ts) BETWEEN str_to_date('"+formatter.format(d1)+"', '%d/%m/%Y') AND str_to_date('"+formatter.format(d2)+"', '%d/%m/%Y')";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<AccountDetailCommand> confirmtradeBookEntries=jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper(AccountDetailCommand.class));
		return confirmtradeBookEntries;

	}
	
	public List<AccountDetailCommand> findAllBillDetails(String userId,ConfirmTradeCommand confirmTrade) throws ParseException {
		String fromDate=confirmTrade.getFromDate();
		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		Date d1=(Date) formatter.parse(fromDate);		
		String sql = "select * from account where user_Id=? and DATE(ts) = str_to_date('"+formatter.format(d1)+"', '%d/%m/%Y')";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<AccountDetailCommand> confirmtradeBookEntries=jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper(AccountDetailCommand.class));
		return confirmtradeBookEntries;
	}

	public List<AccountDetailCommand> findUserAccDetails(String brokerId) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		String sql="select cashl,MARGINL,BALANCED_MARGIN,UTILISED_MARGIN,USER_ID from account where id IN(select max(id) from account where broker_id=? and user_id!=''  group by(user_id))";
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{brokerId},new UserMapper());
		return accounts;
		
	}
	private final class UserMapper implements RowMapper<AccountDetailCommand> {

	    public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	AccountDetailCommand acc = new AccountDetailCommand();
	    	acc.setCashl(rs.getString(1));
	    	acc.setMarginl(rs.getString(2));
	    	acc.setMargin_b(rs.getString(3));
	    	acc.setMargin_u(rs.getString(4));
	    	acc.setUserId(rs.getString(5));
	    	String sql1="SELECT MOBILE,BROKAGE from brokers where user_id='"+acc.getUserId()+"' ";
	    	List contact=jdbcTemplate.query(sql1,new ContactMapper());
	    	Iterator itr=contact.iterator();
	    	while(itr.hasNext()){
	    		AccountDetailCommand acc1=(AccountDetailCommand)itr.next();
	    		acc.setMobile(acc1.getMobile());
	    		acc.setBrokage(acc1.getBrokage());
	    	}	
	        return acc;   
	}
	}

	    private final class ContactMapper implements RowMapper<AccountDetailCommand> {
	    	public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	AccountDetailCommand netpos = new AccountDetailCommand();
		    	netpos.setMobile(rs.getString(1));
		    	netpos.setBrokage(rs.getString(2));
		        return netpos;
		    } 
}
	    
	    
	    public List<AccountDetailCommand> findUserAccount(String userId) {
			jdbcTemplate = new JdbcTemplate(dataSource);
			String sql = "select * from brokers where user_id=?";
			List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper(AccountDetailCommand.class));
			return accounts;

		}

	    
	    public List<AccountDetailCommand> getUserAccount(String userId) {
			jdbcTemplate = new JdbcTemplate(dataSource);
			String sql = "select creditamt,debitamt,remark,DATE(ts),user_Id,CHEQUENO,cashl from account where user_id=?";
			List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{userId},new CashDetailAccountMapper());
	        
			
			return accounts;

		}
	    
	    private final class CashDetailAccountMapper implements RowMapper<AccountDetailCommand> {
	    	public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	AccountDetailCommand acc = new AccountDetailCommand();
		    	acc.setCreditamt(rs.getString(1));
		    	acc.setDebitamt(rs.getString(2));
		    	acc.setRemark(rs.getString(3));
		    	acc.setBillno(rs.getString(4));  //Used to show date
		    	acc.setUserId(rs.getString(5));
		    	acc.setChequeno(rs.getString(6));
		    	acc.setCashl(rs.getString(7));
		    	String openbal = jdbcTemplate.queryForObject( "select openbal from brokers where user_id=?", 
		    	        new Object[]{acc.getUserId()}, String.class);
		    	
				
					acc.setOpenbal(openbal);
				
		        return acc;
		    } 
	    }
	    
	    
	 
	    
	    

	}
	
	
	

